package _3_0.dao;

import _3_0.pojo.Classes;
import _3_0.pojo.Student;
import _3_0.utils.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StudentDAO {
    // 增加
    public void insert(Student s) throws SQLException {

        String sql = "insert into student values (?,?,?,?,?,?)";
        // 1. 得到连接
        Connection conn = JDBCUtils.getConnection();
        // 2. 创建查询对象
        PreparedStatement stmt = conn.prepareStatement(sql);
        stmt.setInt(1, s.getId());
        stmt.setString(2, s.getName());
        stmt.setInt(3, s.getChinese());
        stmt.setInt(4, s.getMath());
        stmt.setInt(5, s.getEnglish());
        stmt.setInt(6, s.getClassId());
        // 3. 执行
        int result = stmt.executeUpdate();
        System.out.println("影响行数:"+result);
        // 4. 释放资源
        JDBCUtils.destory(conn, stmt, null);
    };


    // 删除
    public void deleteById(int id) throws SQLException{
        String sql = "delete from student where id = ?";
        // 1. 得到连接
        Connection conn = JDBCUtils.getConnection();
        // 2. 创建查询对象
        PreparedStatement stmt = conn.prepareStatement(sql);
        stmt.setInt(1, id);
        // 3. 执行
        int result = stmt.executeUpdate();
        System.out.println("影响行数:"+result);
        // 4. 释放资源
        JDBCUtils.destory(conn, stmt, null);
    };



    // 修改
    public void update(Student s) throws SQLException{

        String sql = "update student set name = ?, chinese=?, math=? ,english=? ,classes_id=? where id = ?";
        // 1. 得到连接
        Connection conn = JDBCUtils.getConnection();
        // 2. 创建查询对象
        PreparedStatement stmt = conn.prepareStatement(sql);
        stmt.setString(1, s.getName());
        stmt.setInt(2, s.getChinese());
        stmt.setInt(3, s.getMath());
        stmt.setInt(4, s.getEnglish());
        stmt.setInt(5, s.getClassId());
        stmt.setInt(6, s.getId());
        // 3. 执行
        int result = stmt.executeUpdate();
        System.out.println("影响行数:"+result);
        // 4. 释放资源
        JDBCUtils.destory(conn, stmt, null);
    };

    // 查询
    public Student findById(int id) throws SQLException{
        String sql = "select * from student where id = ?";
        // 1. 得到连接
        Connection conn = JDBCUtils.getConnection();
        // 2. 创建查询对象
        PreparedStatement stmt = conn.prepareStatement(sql);
        stmt.setInt(1, id);
        // 3. 执行
        ResultSet rs = stmt.executeQuery();
        Student s = null;
        if(rs.next())
        {
            String name = rs.getString(2);
            int chinese = rs.getInt(3);
            int math = rs.getInt(4);
            int english = rs.getInt(5);
            int class_id = rs.getInt(6);
            s = new Student(id,name,chinese,math,english,class_id);
        }

        // 4. 释放资源
        JDBCUtils.destory(conn, stmt, null);

        return s;
    }

    // 查询所有
    public List<Student> findAll() throws SQLException{

        String sql = "select * from student";
        // 1. 得到连接
        Connection conn = JDBCUtils.getConnection();
        // 2. 创建查询对象
        PreparedStatement stmt = conn.prepareStatement(sql);
        // 3. 执行
        ResultSet rs = stmt.executeQuery();
        List<Student> list = new ArrayList<>();
        while(rs.next())
        {
            int id = rs.getInt(1);
            String name = rs.getString(2);
            int chinese = rs.getInt(3);
            int math = rs.getInt(4);
            int english = rs.getInt(5);
            int class_id = rs.getInt(6);
            Student s = new Student(id,name,chinese,math,english,class_id);
            list.add(s);
        }

        // 4. 释放资源
        JDBCUtils.destory(conn, stmt, null);

        return list;

    }

}
